/*
 * To change this license header, choose License Headers in Project Properties.
 * To change this template file, choose Tools | Templates
 * and open the template in the editor.
 */
package ServerImp;

import java.sql.*;
import java.util.logging.Level;
import java.util.logging.Logger;
import Shared.User;
import Shared.ClientInt;
import java.rmi.RemoteException;
import java.util.ArrayList;

/**
 *
 * @author belal
 */
public class DatabaseConnection {

    private Connection conn;
    private Statement stmt;
    private ResultSet result;
    private PreparedStatement prepStmt;
    private int userId;
    //private ClientInt []userfriend;
    private User client;

    public DatabaseConnection() {
        try {

            Class.forName("com.mysql.jdbc.Driver");
            conn = DriverManager.getConnection("jdbc:mysql://localhost/Chat?" + "user=root&password=root");
            System.out.println("ddddddd");

        } catch (SQLException ex) {
            // handle any errors
            System.out.println("SQLException: " + ex.getMessage());
            System.out.println("SQLState: " + ex.getSQLState());
            System.out.println("VendorError: " + ex.getErrorCode());
        } catch (ClassNotFoundException ex) {
            ex.printStackTrace();
        }
    }

    public int userMail(String mail) throws RemoteException {
        // To check mail of registered user before signup
        //return 1 if doesn't exist mail
        int cMail = 0;
        try {
            String mSQL = "select email from users where email=?";
            prepStmt = conn.prepareStatement(mSQL);
            prepStmt.setString(1, mail);
            result = prepStmt.executeQuery();
            if (result.next()) {
                cMail = 0;
            } else {
                cMail = 1;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return cMail;
    }

    public String userPass(String mail, String pass) throws RemoteException {
        // To check pass of registered user before signin
        //return 1 if valid password
        int cPass = 0;
        String passwd = new String();
        try {
            String mSQL = "Select user_pass from users where email=?";
            prepStmt = conn.prepareStatement(mSQL);
            prepStmt.setString(1, mail);
            result = prepStmt.executeQuery();
            //System.out.println(result.getString(1));
            if (result.next()) {
                passwd = result.getString(1);
                if (pass.equals(passwd)) {
                    cPass = 1;
                } else {
                    cPass = 0;
                }
            } else {
                passwd = "false";
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return passwd;
    }

    public int registerUser(User user) throws RemoteException {
        System.out.println("hfhfhfhfhf");
        int returnValue = 0;
        try {
            String inSQL = "insert into users"
                    + "(first_name, last_name, email,user_pass,birth_date,country,user_name) VALUES"
                    + "(?,?,?,?,?,?,?)";
            String chSql = "Select email from users where email=?";
            prepStmt = conn.prepareStatement(chSql);
            prepStmt.setString(1, user.getUserMail());
            result = prepStmt.executeQuery();
            if (result.next()) { //check for founded user mail
                returnValue = 0;
            } else {
                prepStmt = conn.prepareStatement(inSQL);
                prepStmt.setString(1, user.getUserFName());
                prepStmt.setString(2, user.getUserLname());
                prepStmt.setString(3, user.getUserMail());
                prepStmt.setString(4, user.getUserPass());
                prepStmt.setString(5, user.getUserDate());
                prepStmt.setString(6, user.getUserCountry());
                prepStmt.setString(7, user.getUserName());
                returnValue = prepStmt.executeUpdate();//return 1 for inserting new user
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return returnValue;
    }

    public int loginUser(User user) {
        int returnValue = 0;
        System.out.println(user.getUserMail());
        System.out.println(user.getUserPass());
        try {
            System.out.println("database login");
            //stmt=conn.createStatement();
            String query = "select email from users where email = ? And user_pass=?";
            prepStmt = conn.prepareStatement(query);
            prepStmt.setString(1, user.getUserMail());
            prepStmt.setString(2, user.getUserPass());
            result = prepStmt.executeQuery();
            if (result.next()) {
                returnValue = 1;
                //System.out.println(result.getString("user_name"));
            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        System.out.println("re" + returnValue);
        return returnValue;
    }
    public ArrayList<String> getAllMail()
    {
        ArrayList<String> mails = new ArrayList<>();
        String query = "select email from users";
        try {
            prepStmt = conn.prepareStatement(query);
            result = prepStmt.executeQuery();
            if (result.next()) {
                mails.add(result.getString("email"));
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return mails;
    }
    public ArrayList<User> getUserFreinds(String mail) {
        ArrayList<User> friends = new ArrayList<>();
        try {
            String myUser = "Select first_name,last_name,email,user_name,status from users where email=?";
            prepStmt = conn.prepareStatement(myUser);
            prepStmt.setString(1, mail);
            ResultSet rs2 = prepStmt.executeQuery();
            while (rs2.next()) {
                client = new User();
                client.setUserFName(rs2.getString("first_name"));
                client.setUserLname(rs2.getString("last_name"));
                client.setUserName(rs2.getString("user_name"));
                client.setUserMail(rs2.getString("email"));
                client.setStatus(rs2.getInt("status"));
                friends.add(client);
            }
            String SqlFriend = "select Sender as test from friends where Reciever=? "
                    + "UNION"
                    + " select Reciever as test from friends where Sender=?";
            prepStmt = conn.prepareStatement(SqlFriend);
            prepStmt.setString(1, mail);
            prepStmt.setString(2, mail);
            result = prepStmt.executeQuery();
            while (result.next()) {
                String friend = result.getString("test");
                String query = "Select first_name,last_name,email,user_name,status from users where email=?";
                prepStmt = conn.prepareStatement(query);
                prepStmt.setString(1, friend);
                ResultSet rs = prepStmt.executeQuery();
                while (rs.next()) {
                    client = new User();
                    client.setUserFName(rs.getString("first_name"));
                    client.setUserLname(rs.getString("last_name"));
                    client.setUserName(rs.getString("user_name"));
                    client.setUserMail(rs.getString("email"));
                    client.setStatus(rs.getInt("status"));

                    friends.add(client);
                }

            }

        } catch (Exception e) {
            e.printStackTrace();
        }
        return friends;
    }

    public void changeStatus(String mail, int status) {
        String Sql = "update users set status=? where email=?";
        int returnValue = 0;
        try {
            prepStmt = conn.prepareStatement(Sql);
            prepStmt.setInt(1, status);
            prepStmt.setString(2, mail);
            returnValue = prepStmt.executeUpdate();
        } catch (SQLException ex) {
            System.out.println("change Status");
            ex.printStackTrace();
        }
    }

    public ArrayList<User> addFreind(String sender, String reciever) {
        ArrayList<User> frinList = new ArrayList<>();
        String inSQL = "insert into FriendRequests"
                + "(Sender,Reciever) VALUES"
                + "(?,?)";
        String chSQL = "SELECT * FROM friends WHERE Sender=? and Reciever=?"
                + "UNION" + " select * from friends where Reciever=? and Sender=?";
        int returnValue = 0;
        int returnVal = 0;
        try {
            prepStmt = conn.prepareStatement(chSQL);
            prepStmt.setString(1, sender);
            prepStmt.setString(2, reciever);
            prepStmt.setString(3, sender);
            prepStmt.setString(4, reciever);
            result = prepStmt.executeQuery();
            if (result.next()) {
                returnValue = 0;
            } else {
                prepStmt = conn.prepareStatement(inSQL);
                prepStmt.setString(1, sender);
                prepStmt.setString(2, reciever);
                returnVal = prepStmt.executeUpdate();
                if (returnVal > 0) {
                    frinList = getUserFreinds(sender);
                }
            }
        } catch (Exception e) {
            System.out.println("FriendRequest");
            e.printStackTrace();
        }
        return frinList;
    }
    public int addFreindOffline(String sender,String reciever){
       int returnVal=0;
        try {
            String inSQL = "insert into FriendRequests"
                    + "(Sender,Reciever) VALUES"
                    + "(?,?)";
            prepStmt = conn.prepareStatement(inSQL);
            prepStmt.setString(1, sender);
            prepStmt.setString(2, reciever);
            returnVal = prepStmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return returnVal;
                
    }

    public User updateFriendRequest(String sender, String reciever) {
        User frinUser = null;
        String inSQL = "insert into friends"
                + "(Sender,Reciever) VALUES"
                + "(?,?)";
        try {
            prepStmt = conn.prepareStatement(inSQL);
            prepStmt.setString(1, sender);
            prepStmt.setString(2, reciever);
            int returnval = prepStmt.executeUpdate();
            if (returnval > 0) {
                frinUser=getUser(reciever);
            }

        } catch (SQLException ex) {
            System.out.println("updateFriendRequest");
            ex.printStackTrace();
        }
        return frinUser;
    }

    public int getUserCount() {
        String query = "select email from users";
        int rowCount = 0;
        try {
            prepStmt = conn.prepareStatement(query);
            result = prepStmt.executeQuery();
            while (result.next()) {
                String id = result.getString(1);
            }
            result.last();
            rowCount = result.getRow();
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return rowCount;
    }

    public ArrayList<User> getFriendRequest(String mail) {
        String chSQL = "SELECT Sender FROM FriendRequests WHERE  Reciever=?";
        ArrayList<User> sender = new ArrayList<>();
        try {
            prepStmt = conn.prepareStatement(chSQL);
            prepStmt.setString(1, mail);
            result = prepStmt.executeQuery();
            while (result.next()) {
                String senMail = result.getString("Sender");
                sender.add(getUser(senMail));
            }
        } catch (SQLException ex) {
            ex.printStackTrace();
        }
        return sender;
    }

    public ArrayList<User> updateFriendRequests(String sender, String reciever) {
        ArrayList<User> friList = new ArrayList<>();
        String inSQL = "insert into friends"
                + "(Sender,Reciever) VALUES"
                + "(?,?)";
        String delSql = "delete from FriendRequests where Sender=? and Reciever=?";
        try {
            prepStmt = conn.prepareStatement(inSQL);
            prepStmt.setString(1, sender);
            prepStmt.setString(2, reciever);
            int returnval = prepStmt.executeUpdate();
            if (returnval > 0) {
                prepStmt = conn.prepareStatement(delSql);
                prepStmt.setString(1, sender);
                prepStmt.setString(2, reciever);
                prepStmt.executeUpdate();
                friList.add(getUser(reciever));
            }

        } catch (SQLException ex) {
            System.out.println("updateFriendRequest");
            ex.printStackTrace();
        }
        return friList;
    }

    public User getUser(String mail) {
        User user=null;
        try {
            
            String query = "Select first_name,last_name,email,user_name,status from users where email=?";
            prepStmt = conn.prepareStatement(query);
            prepStmt.setString(1,mail);
            ResultSet rs = prepStmt.executeQuery();
            while (rs.next()) {
                client = new User();
                client.setUserFName(rs.getString("first_name"));
                client.setUserLname(rs.getString("last_name"));
                client.setUserName(rs.getString("user_name"));
                client.setUserMail(rs.getString("email"));
                client.setStatus(rs.getInt("status"));
                user=client;
            }
        } catch (SQLException ex) {
            Logger.getLogger(DatabaseConnection.class.getName()).log(Level.SEVERE, null, ex);
        }
        return user;

    }

}
